import pandas as pd
import json
import numpy as np



def query_raw_req(order_num, mysql_con):
    """
    根据订单号从数据库匹配相应的数据
    """
    # 从订单表种查询基础信息
    order_sql = f"select * from loan_order o  WHERE  o.order_num = '{order_num}' "
    order_info = pd.read_sql(order_sql, mysql_con, dtype={
        'apply_time': str, 'last_audit_time': str, 'outpay_time': str, 'should_inpay_date': str,
        'should_inpay_date': str,
        'accountable_post_loan_assign_time': str, 'commit_inpay_time': str, 'tracking_time': str, 'settle_time': str,
        'created_at': str, 'updated_at': str}).to_dict(orient='records')[0]

    user_id = order_info['user_id']
    app_order_id = order_info['order_num']
    apply_time = order_info['apply_time']
    new_old_sign = order_info['new_old_sign']
    phone = order_info['phone']

    # 查询历史订单信息
    try:
        sql = f"select * from loan_order where user_id = {user_id} and apply_time < '{apply_time}' "
        order_data = pd.read_sql(sql, mysql_con, dtype={
            'apply_time': str, 'last_audit_time': str, 'outpay_time': str, 'should_inpay_date': str,
            'should_inpay_date': str,
            'accountable_post_loan_assign_time': str, 'commit_inpay_time': str, 'tracking_time': str,
            'settle_time': str,
            'created_at': str, 'updated_at': str}).to_dict(orient='records')
    except:
        order_data = []

    try:
        sql = f'select * from user where id = {user_id}'
        user_info = pd.read_sql(sql, mysql_con, dtype={'created_at': str, 'updated_at': str}).to_dict(orient='records')[0]
    except:
        user_info = {}

    # 查询 risk_applicant 数据
    try:
        risk_sql = f"select * from risk_applicant where order_num = '{order_num}' and latest = 1 "
        risk_application = pd.read_sql(risk_sql, mysql_con).to_dict(orient='records')[0]
        risk_application = json.loads(risk_application['applicant'])
    except:
        risk_application = {}

    # 查询applist数据并进行转化
    try:
        applist_sql = f"""
        SELECT
           a.* 
        FROM
            loan_order o
            JOIN analysis_user_app_detail a ON o.app_info_mongo_link = a.third_id 
        WHERE
            o.order_num = '{order_num}'
        """
        applist_data = pd.read_sql(applist_sql, mysql_con,
                                   dtype={'first_install_time': str, 'last_update_time': str}).to_dict(orient='records')
    except:
        applist_data = []

    # 查询短信数据
    try:
        sms_sql = f"""
                SELECT
                   a.* 
                FROM
                    loan_order o
                    JOIN analysis_user_sms_detail a ON o.sms_info_mongo_link = a.third_id 
                WHERE
                    o.order_num = '{order_num}'
                """
        sms_data = pd.read_sql(sms_sql, mysql_con).to_dict(orient='records')
    except:
        sms_data = []

    # 查询calllog数据
    try:
        calllog_sql = f"""
                SELECT
                   a.* 
                FROM
                    loan_order o
                    JOIN analysis_user_calllog_detail a ON o.call_log_info_mongo_link = a.third_id
                WHERE
                    o.order_num = '{order_num}'
                """
        calllog_data = pd.read_sql(calllog_sql, mysql_con, dtype={'contact_time': str}).to_dict(orient='records')
    except:
        calllog_data = []

    # 查询联系人信息
    try:
        contact_sql = f"""
        select a.* from
            loan_order o
            JOIN user_emergency_contact_info a on o.user_id = a.user_id
        where o.order_num =  '{order_num}' and a.created_at <= o.apply_time
                """
        contact_list = pd.read_sql(contact_sql, mysql_con, dtype={'created_at': str, 'updated_at': str}).to_dict(
            orient='records')
    except:
        contact_list = []



    request_params = {
        'base_info': {'order_id': app_order_id,
                      'apply_time': apply_time,
                      'phone': phone,
                      'user_id': user_id,
                      'new_old_sign':new_old_sign,
                      },
        'data_sources': {
            'order_info': order_info,
            'applist_data': applist_data,
            'sms_data': sms_data,
            'contact_list': contact_list,
            'calllog_data': calllog_data,
            'order_data': order_data,
            'user_info': user_info
        },
        'third_feature':{
            'creditfeature_v3':{}
        },
        'applicant': risk_application
    }
    return request_params


def raw2_request_params(request_json):
    base_info = request_json['base_info']
    base_info['tx_id'] = base_info['order_id']
    base_info['country_abbr'] = 'id'
    base_info['country_code'] = "62"

    data_sources = request_json['data_sources']
    applist_data = data_sources['applist_data']
    sms_data = data_sources['sms_data']
    contact_list = data_sources['contact_list']
    calllog_data = data_sources['calllog_data']

    order_data = data_sources['order_data']

    user_info = data_sources['user_info']
    order_info = data_sources['order_info']

    if len(applist_data) > 0:
        user_apps = pd.DataFrame(applist_data)
    else:
        user_apps = pd.DataFrame(applist_data,
                                 columns=['pkg_name', 'app_name', 'first_install_time', 'last_update_time'])
    user_apps = user_apps.rename(columns={
        'pkg_name': 'app_package',
        'app_name': 'app_name',
        'first_install_time': 'fi_time',
        'last_update_time': 'lu_time',
    })
    user_apps['isSystem'] = 0
    user_apps['fi_time'] = pd.to_datetime(user_apps['fi_time'], format='mixed').apply(
        lambda x: int(x.timestamp() * 1000))
    user_apps['lu_time'] = pd.to_datetime(user_apps['lu_time'], format='mixed').apply(
        lambda x: int(x.timestamp() * 1000))
    user_apps = user_apps[['app_package', 'app_name', 'fi_time', 'lu_time', 'isSystem']].to_dict(orient='records')

    if len(sms_data):
        user_sms = pd.DataFrame(sms_data)
    else:
        user_sms = pd.DataFrame(sms_data, columns=['content', 'phone', 'time', 'type'])
    user_sms['phone'] = user_sms['phone'].fillna('')
    user_sms = user_sms.rename(columns={
        'content': 'body',
        'phone': 'src_phone',
        'time': 'time',
        'type': 'type'
    })
    user_sms['phone'] = user_sms['src_phone']
    user_sms['type'] = user_sms['type'].astype(str)
    user_sms['read'] = '0'
    user_sms['time'] = pd.to_datetime(user_sms['time'], format='mixed').apply(
        lambda x: int(x.timestamp() * 1000)).astype(str)
    user_sms = user_sms[['body', 'phone', 'read', 'src_phone', 'time', 'type']].to_dict(orient='records')

    if len(calllog_data) > 0:
        user_colllog = pd.DataFrame(calllog_data)
    else:
        user_colllog = pd.DataFrame(calllog_data, columns=['contact_number', 'contact_time', 'duration', 'contact_name',
                                                           'contact_comment', 'type'])
    user_colllog = user_colllog.rename(columns={
        'contact_number': 'call_log_number',
        'duration': 'call_log_duration',
        'contact_name': 'call_log_address_name',
        'contact_comment': 'call_log_attribution'
    })
    callog_mapping = {
        '1': 'incoming_call',
        '2': 'outgoing_call',
        '3': 'missed_call',
        '5': 'cancellation_call',
        '6': 'cancellation_call',
        '7': 'cancellation_call',
        '10': 'cancellation_call',
        '20': 'cancellation_call',
        '25': 'cancellation_call',
        '26': 'cancellation_call',
        '27': 'cancellation_call',
        '-3': 'cancellation_call',
        '-5': 'cancellation_call',
        '-2': 'cancellation_call',
        '-1': 'cancellation_call',
        '-10': 'cancellation_call',
        '-25': 'cancellation_call',
        '-27': 'cancellation_call',
    }
    user_colllog['call_log_address_name'] = user_colllog['call_log_address_name'].fillna('')
    user_colllog['call_log_attribution'] = user_colllog['call_log_attribution'].fillna('')
    user_colllog['call_log_connection_status'] = user_colllog['type'].astype(str).map(callog_mapping).fillna(
        'cancellation_call')
    user_colllog['calloranswer_time'] = pd.to_datetime(user_colllog['contact_time'], format='mixed').apply(
        lambda x: int(x.timestamp() * 1000)).astype(str)
    user_colllog = user_colllog[['call_log_address_name', 'call_log_attribution', 'call_log_connection_status',
                                 'call_log_duration', 'call_log_number', 'calloranswer_time']].to_dict(orient='records')
    if len(contact_list) > 0:
        user_contact = pd.DataFrame(contact_list)
    else:
        user_contact = pd.DataFrame(contact_list, columns=['user_id', 'raw_name', 'phone', 'relation'])
    user_contact = user_contact.rename(columns={
        'user_id': 'appUserId',
        'raw_name': 'contactName',
        'phone': 'contactPhoneNumber',
        'relation': 'contactRelationship'
    })
    user_contact = user_contact[['appUserId', 'contactName', 'contactPhoneNumber', 'contactRelationship']].to_dict(
        orient='records')

    user_info_1 = {
        "userInfo": {
            "userRecord": {
                'appUserId': user_info['id'],
                'registerTime': user_info['created_at'],
                'createTime': user_info['created_at']
            }
        },
        'productInfo': {
            'productCode': str(order_info['product_id']),
            'amount': order_info['principal_amount'],
            'createTime': order_info['apply_time']
        }
    }

    order_data_a = order_data_a_parser(order_data)
    installments_data_a = installments_data_a_parser(order_data)
    contract_data_a = contract_data_a_parser(order_data)
    return {
        'base_info': base_info,
        'data_sources': {
            'applist_data': user_apps,
            'sms_data': user_sms,
            'contact_list': user_contact,
            'calllog_data': user_colllog,
            'user_info_1': user_info_1,
            'order_data_a':order_data_a,
            'installments_data_a':installments_data_a,
            'contract_data_a': contract_data_a
        },
        'applicant': request_json['applicant'],
        'third_feature':request_json['third_feature']
    }

def contract_data_a_parser(order_data):
    # contract_data_a = pd.DataFrame(order_data)
    return {}


def installments_data_a_parser(order_data):
    if len(order_data)==0:
        return []
    installments_data_a = pd.DataFrame(order_data)
    installment_stats = ['TO_BE_INPAY', 'OVERDUE_TO_BE_INPAY', 'NORMAL_INPAY', 'OVERDUE_INPAY']
    installments_data_a = installments_data_a[installments_data_a['status'].isin(installment_stats)]
    installments_data_a['status0'] = installments_data_a['status']
    installments_data_a['id'] = installments_data_a['id']
    installments_data_a['user_id'] = installments_data_a['user_id']
    installments_data_a['acq_channel'] = installments_data_a['channel']
    installments_data_a['app_order_id'] = installments_data_a['order_num']
    installments_data_a['contract_no'] = installments_data_a['app_order_id']
    installments_data_a['repayment_date'] = installments_data_a['should_inpay_date']
    installments_data_a['installment_num'] = 1
    installments_data_a['installment_amount'] = installments_data_a['principal_amount']
    installments_data_a['principal'] = installments_data_a['outpay_amount']
    installments_data_a['cut_interest'] = installments_data_a['principal_amount'] - installments_data_a['outpay_amount']
    installments_data_a['service_fee'] = 0
    installments_data_a['interest'] = 0
    installments_data_a['management_fee'] = 0
    installments_data_a['overdue_interest'] = 0
    installments_data_a['penalty'] = 0
    installments_data_a['extension_fee'] = installments_data_a['fee_extension_should_inpay'].fillna(0)
    installments_data_a['repaid_principal'] = installments_data_a['inpaid_amount_when_settle'].fillna(0)
    installments_data_a['repaid_interest'] = 0
    installments_data_a['repaid_cut_interest'] = 0
    installments_data_a['repaid_service_fee'] = 0
    installments_data_a['repaid_management_fee'] = 0
    installments_data_a['repaid_overdue_interest'] = 0
    installments_data_a['repaid_penalty'] = 0
    installments_data_a['discount_amount'] = 0
    installments_data_a['waive_amount'] = 0
    installments_data_a['waive_amount'] = 0
    installments_data_a['overdue_days'] = installments_data_a['overdue_days']
    installments_data_a['status'] = np.where(installments_data_a['settle_time'].notnull(), 2, 1)

    def gen_settlement_type(row):
        settle_time = row.get('settle_time', None)
        extended = row.get('extended', 0)
        if pd.notnull(settle_time):
            if extended == 1:
                return 2
            else:
                return 1
        else:
            return 0

    installments_data_a['settlement_type'] = installments_data_a.apply(gen_settlement_type, axis=1)
    installments_data_a['extension_count'] = installments_data_a['order_num'].str.extract(r'-(\d+)$').fillna(0).astype(
        int)
    installments_data_a['new_old_user_status'] = installments_data_a['new_old_sign'].map(
        lambda x: 2 if x == 'OLD' else 0)
    installments_data_a['create_time'] = installments_data_a['outpay_time']
    installments_data_a['update_time'] = np.where(installments_data_a['settle_time'].notnull(),
                                                  installments_data_a['settle_time'],
                                                  installments_data_a['create_time'])
    installments_data_a['settlement_time'] = installments_data_a['settle_time']


    return  installments_data_a[['id',
        'user_id',
        'acq_channel',
        'app_order_id',
        'contract_no',
        'repayment_date',
        'installment_num',
        'installment_amount',
        'principal',
        'interest',
        'cut_interest',
        'service_fee',
        'management_fee',
        'overdue_interest',
        'overdue_days',
        'penalty',
        'extension_fee',
        'repaid_principal',
        'repaid_interest',
        'repaid_cut_interest',
        'repaid_service_fee',
        'repaid_management_fee',
        'repaid_overdue_interest',
        'repaid_penalty',
        'discount_amount',
        'waive_amount',
        'settlement_type',
        'extension_count',
        'new_old_user_status',
        'status',
        'create_time',
        'update_time']].to_dict(orient='records')

def order_data_a_parser(order_data):
    if len(order_data)==0:
        return []
    order_data_a = pd.DataFrame(order_data)
    order_data_a = order_data_a[order_data_a['outpay_amount'] > 0]
    order_data_columns = ['id',
                          'app_order_id',
                          'user_id',
                          'acq_channel',
                          'device_type',
                          'product_name',
                          'product_code',
                          'product_set_code',
                          'loan_amount',
                          'down_payment',
                          'user_name',
                          'id_card_number',
                          'phone_number',
                          'apply_time',
                          'personal_info',
                          'work_info',
                          'contact_info',
                          'bank_account_info',
                          'ktp_info',
                          'face_recognition_info',
                          'product_info',
                          'app_track_id',
                          'status',
                          'rejected_until',
                          'new_old_user_status',
                          'create_time',
                          'update_time',
                          'submit_type']

    order_data_a['product_info'] = None
    order_data_a['personal_info'] = None
    order_data_a['work_info'] = None
    order_data_a['contact_info'] = None
    order_data_a['bank_account_info'] = None
    order_data_a['ktp_info'] = None
    order_data_a['face_recognition_info'] = None
    order_data_a['rejected_until'] = None
    order_data_a['app_track_id'] = None

    order_data_a['id'] = order_data_a['id']
    order_data_a['app_order_id'] = order_data_a['order_num']
    order_data_a['user_id'] = order_data_a['user_id']
    order_data_a['acq_channel'] = order_data_a['channel']
    order_data_a['device_type'] = order_data_a['device_type']
    order_data_a['product_name'] = order_data_a['product_name']
    order_data_a['product_code'] = order_data_a['product_id'].astype(str)
    order_data_a['product_set_code'] = order_data_a['product_id'].astype(str)
    order_data_a['loan_amount'] = order_data_a['principal_amount']
    order_data_a['down_payment'] = 0
    order_data_a['user_name'] = order_data_a['name']
    order_data_a['id_card_number'] = order_data_a['user_id']
    order_data_a['phone_number'] = order_data_a['phone']
    order_data_a['apply_time'] = order_data_a['apply_time'].map(lambda x: str(x)[0:20])
    order_data_a['new_old_user_status'] = order_data_a['new_old_sign'].map(lambda x: 2 if x == 'OLD' else 0)
    order_data_a['create_time'] = order_data_a['created_at']
    order_data_a['update_time'] = order_data_a['last_audit_time']
    order_data_a['submit_type'] = 1
    order_data_a['status'] = order_data_a['status'].map(lambda x: 22 if x == 'AUDIT_REJECT' else 21)
    order_data_a = order_data_a[order_data_columns]
    return order_data_a.to_dict(orient='records')

if __name__ == '__main__':
    from sqlalchemy import create_engine
    import requests
    db_con = create_engine(f'mysql+pymysql://xl_prod_riskwh:X!b0dr1Wnh@8.219.235.84:3306/xl_prod')
    order_num_new='OD250309133501200659412'
    order_num_old='OD250201115811124938074'
    qr = query_raw_req(order_num_new,db_con)
    # req_info = raw2_request_params(qr)
    url = 'http://127.0.0.1:8089/api/a2_score'
    rep_obj = requests.post(url, json=qr)
    print(rep_obj.json())
    print(rep_obj.json()['monitor_features']['a2_score_real'])
    print(rep_obj.json()['monitor_features']['a5_score_real'])
    pass
